﻿using OestsCommon.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace OestsDataBase
{
    /// <summary>
    /// 学生操作题考试信息处理
    /// </summary>
    public static class StudentExamDAL
    {
        #region 学员


        /// <summary>
        /// 检查学生是否参加了操作题考试
        /// </summary>
        /// <param name="studentId"></param>
        /// <param name="examId"></param>
        /// <returns></returns>
        public static bool CheckStudentisInExam(int studentId, int examId)
        {
            string sql = string.Format("if exists(select* from T_StudentExamData where studentID={0} AND exam_id = {1}) select 1 else select 0", studentId, examId);
            //   string sql = string.Format("select studentID FROM T_Student_Operate where studentID={0} AND paperID = {1}", studentId, paperId);
            int res = (int)SqlHelper.ExecuteScalar(sql);
            return res == 1;
        }

        public static List<ExamLog> GetExamErrorList(int examId, int studentId)
        {
            string strSql = string.Format("select * from T_StudentExamData where studentID ={0} and exam_id={1}", studentId, examId);
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) return null;
            List<ExamLog> list = new List<ExamLog>();

            if (!DBNull.Value.Equals(dt.Rows[0]["exam_log"]))//TODO::根据考试类型的不同，解析操作记录
            {
                string json = dt.Rows[0]["exam_log"].ToString();
                list = LitJson.JsonMapper.ToObject<List<ExamLog>>(json);
            }
            return list;
        }

        /// <summary>
        /// 学员获取考试成绩数据
        /// </summary>
        /// <param name="stdId"></param>
        /// <returns></returns>
        public static List<ExamData> StudentGetExamData(int stdId)
        {
            string strSql = string.Format("select * from T_StudentExamData where T_StudentExamData.studentID={0} ", stdId);
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) return null;
            List<ExamData> list = new List<ExamData>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(ReadExamData(dr));
            }
            return list;
        }

        public static ExamData ReadExamData(DataRow dr)
        {
            ExamData ed = new ExamData();
            ed.StudentId = Int32.Parse(dr["studentID"].ToString());
            ed.ExamId = Int32.Parse(dr["exam_id"].ToString());
            ed.Grade = DBNull.Value.Equals(dr["grade"]) ? 0 : Int32.Parse(dr["grade"].ToString());
            // ed.OperateErrorList = Int32.Parse(dr["wrongs"].ToString());
            ed.Date = DBNull.Value.Equals(dr["exam_date"]) ? "" : dr["exam_date"].ToString();
            ed.FinishTime = DBNull.Value.Equals(dr["exam_date"]) ? 0 : int.Parse(dr["finishTime"].ToString());

            if (!DBNull.Value.Equals(dr["exam_log"]))//TODO::根据考试类型的不同，解析操作记录
            {
                string json = dr["exam_log"].ToString();
                ed.ResultLogInfo = LitJson.JsonMapper.ToObject<List<ExamLog>>(json);
            }
            ed.Count = DBNull.Value.Equals(dr["count"]) ? 0 : int.Parse(dr["count"].ToString());
            return ed;
        }

        public static Paper StudentGetPaper(int projectID, int paperID)
        {
            string sql = string.Format("select T_Paper.paper_id as paper_id, T_Paper.paper_name as paper_name,T_Paper.time as paperTime,T_PaperOperate_{0}.*,T_Task_{1}.*" +
                " from T_Paper,T_Paper_{0},T_Task_{1} WHERE T_Paper.paper_id={0} and T_Paper_{0}.task_id = T_Task_{1}.id  order by T_Task_{1}.id", paperID, projectID);
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt == null) return null;
            Paper p = new Paper();
            //  p.Tasks = new List<TaskModel>();
            bool initBaseData = false;
            foreach (DataRow dr in dt.Rows)
            {
                if (!initBaseData)
                {
                    p.Id = Int32.Parse(dr["paper_id"].ToString());
                    p.Name = dr["paper_name"].ToString();
                    // p.Time = Int32.Parse(dr["paperTime"].ToString());
                    // p.ProjectId = Int32.Parse(dr["projectId"].ToString());
                    //  p.TeacherId = Int32.Parse(dr["teacher_id"].ToString());
                    initBaseData = true;
                }
                TaskModel t = TaskDAL.ReadData(dr);
                t.StepScore = dr["score"].Equals(DBNull.Value) ? "" : dr["score"].ToString();
                // p.Tasks.Add(t);
            }
            return p;
        }

        /// <summary>
        /// 学员提交考试成绩
        /// </summary>
        /// <param name="data"></param>
        public static bool HandInExamGrade(ExamData data)
        {
            string errorJson = LitJson.JsonMapper.ToJson(data.ResultLogInfo);
            string strSql1 = string.Format("update T_StudentExamData set grade={0}, exam_log='{1}', exam_date='{2}' ,count = {3},finishTime={4} where studentID=@student and exam_id = @exam_id",
                data.Grade, errorJson, data.Date, 1, data.FinishTime);
            int n = SqlHelper.ExecuteNonQuery(strSql1, new SqlParameter("@student", data.StudentId), new SqlParameter("@exam_id", data.ExamId));
            return n == 1;//如果考试信息表中没得当前学员&试卷 提交失败
        }

        public static bool UpdateExamCount(int stdId, int examId)
        {
            string strSql = string.Format("select * from T_StudentExamData where studentID={0} and exam_id ={1} ", stdId, examId);
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null || dt.Rows.Count == 0) return false;

            int count = dt.Rows[0]["count"].Equals(DBNull.Value) ? 0 : Int32.Parse(dt.Rows[0]["count"].ToString());
            count++;

            string strSql1 = string.Format("update T_StudentExamData set count={0} where studentID=@student and exam_id = @exam_id", count);

            int n = SqlHelper.ExecuteNonQuery(strSql1, new SqlParameter("@student", stdId), new SqlParameter("@exam_id", examId));
            return n == 1;
        }

        #endregion

        #region 教员


        /// <summary>
        /// 添加学员到考试中
        /// </summary>
        /// <param name="examid"> 考试id</param>
        /// <param name="stdids">学生id集合</param>
        /// <returns></returns>
        public static bool AddStudentToExam(int examid, List<int> stdids)
        {
            if (stdids == null || stdids.Count == 0) return false;
            string dataStr = "";
            foreach (int stdId in stdids)
            {
                dataStr += " select " + stdId + "," + examid + " union";
            }
            dataStr = dataStr.Substring(0, dataStr.Length - 5);
            string sql = string.Format("insert into T_StudentExamData (studentID,exam_id) " + dataStr);
            int n = SqlHelper.ExecuteNonQuery(sql);
            return n > 0;
        }

        public static bool AddStudentToExam(int examid, int stdid)
        {
            string sql = string.Format("insert into T_StudentExamData (studentID,exam_id,count) values({0},{1},{2}) ", stdid, examid, 0);
            int n = SqlHelper.ExecuteNonQuery(sql);
            return n > 0;
        }

        /// <summary>
        /// 删除某个学员ID的所有考试数据
        /// </summary>
        /// <param name="stuId"></param>
        /// <returns></returns>
        public static bool DeleteStudentInfo(int stuId)
        {
            string sql = string.Format("delete from T_StudentExamData where studentID={0}", stuId);
            int n = SqlHelper.ExecuteNonQuery(sql);
            return true;
        }

        public static bool DeleteStudentInfos(List<int> stuIds)
        {
            if (stuIds == null || stuIds.Count == 0) return true;
            string idStr = string.Join(",",stuIds);
            string sql = string.Format("delete from T_StudentExamData where studentID in ({0})", idStr);
            int n = SqlHelper.ExecuteNonQuery(sql);
            return n>0;
        }



        /// <summary>
        /// 根据试卷删除考试信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DeleteExamInfoByPaper(int id)
        {
            string sql = string.Format("delete from T_StudentExamData Where T_StudentExamData.exam_id in(select id from T_Exam where paper_id ={0}) ", id);

            // string sql1 = string.Format("delete from T_StudentExamData where exam_id={0}", id);
            int n = SqlHelper.ExecuteNonQuery(sql);
            if (n > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 删除考试后删除对应加入该考试的学员
        /// </summary>
        /// <param name="project"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DeleteExamInfoByExam(int id)
        {
            string sql = string.Format("delete from T_StudentExamData where exam_id={0}", id);
            int n = SqlHelper.ExecuteNonQuery(sql);
            if (n > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 删除教师后，删除被删除教师创建的考试信息
        /// </summary>
        /// <param name="project"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DeleteExamInfoByTeacher(int id)
        {
            string sql = string.Format("delete from T_StudentExamData where T_StudentExamData.exam_id in(select id from T_Exam where teacherid={0})", id);
            int n = SqlHelper.ExecuteNonQuery(sql);
            if (n > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 从考试信息表中移除学生
        /// </summary>
        /// <param name="examId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        public static bool RemoveStudentFromExam(int examId, int studentId)
        {
            //string stds = "";
            //foreach (var item in students)
            //{
            //    stds += item + ",";
            //}
            // stds = stds.Substring(0, stds.Length - 1);
            string sql = string.Format("delete from T_StudentExamData where exam_id={0} and studentID={1}", examId, studentId);
            int n = SqlHelper.ExecuteNonQuery(sql);

            return n > 0;
        }

        /// <summary>
        /// 查询所有考试的成绩集合
        /// </summary>
        /// <returns></returns>
        public static List<ExamData> GetExamDatas()
        {
            // string sql = string.Format("select T_Student_Operate.* " +
            //  "from T_PaperOperates, T_Student_Operate WHERE T_PaperOperates.teacher_id ={0} and T_PaperOperates.id = T_Student_Operate.exam_id", teacherID);
            string sql = string.Format("select * from T_StudentExamData ");
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt == null) return null;
            List<ExamData> list = new List<ExamData>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(ReadExamData(dr));
            }
            return list;

        }
        /// <summary>
        /// 查询对应id的考试成绩集合
        /// </summary>
        /// <returns></returns>
        public static List<ExamData> GetExamDatasByID(int examid)
        {
            // string sql = string.Format("select T_Student_Operate.* " +
            //  "from T_PaperOperates, T_Student_Operate WHERE T_PaperOperates.teacher_id ={0} and T_PaperOperates.id = T_Student_Operate.exam_id", teacherID);
            string sql = string.Format("select * from T_StudentExamData where exam_id={0}", examid);
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt == null) return null;
            List<ExamData> list = new List<ExamData>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(ReadExamData(dr));
            }
            return list;

        }

        /// <summary>
        /// 查询教师管理的所有试卷的成绩集合
        /// </summary>
        /// <param name="teacherID"></param>
        /// <returns></returns>
        public static List<ExamData> TeacherGetExamDatas(int teacherID)
        {
            // string sql = string.Format("select T_Student_Operate.* " +
            //  "from T_PaperOperates, T_Student_Operate WHERE T_PaperOperates.teacher_id ={0} and T_PaperOperates.id = T_Student_Operate.exam_id", teacherID);
            string sql = string.Format("select T_StudentExamData.* " +
               "from T_Exam, T_StudentExamData WHERE T_Exam.teacherid ={0} and T_Exam.id = T_StudentExamData.exam_id", teacherID);

            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt == null) return null;
            List<ExamData> list = new List<ExamData>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(ReadExamData(dr));
            }
            return list;

        }


        public static ExamData StudentGetTheoryExamLog(DP<int, int> data)
        {
            string sql = string.Format("select * from  T_StudentExamData WHERE studentID ={0} and exam_id = {1}", data.Value1, data.Value2);

            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt == null) return null;

            ExamData examData = ReadExamData(dt.Rows[0]);

            return examData;
        }
        #endregion
    }
}
